package hit.dao.database;

import hit.core.Barcode;
import hit.core.HITBase;
import hit.core.Product;
import hit.core.Size;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

/**
 * Data Access Object that accesses {@link Product}s in the database
 */
public class DBProductDAO extends DBBase {

	
	/**
	 * @param databaseDAO a pointer to the DatabaseDAO that created this
	 * DBStorageUnitDAO
	 * */
	public DBProductDAO(DatabaseDAO databaseDAO){
		super(databaseDAO);
	}
	
	@Override
	protected PreparedStatement InitGetQuery() throws SQLException {
		String sql = "SELECT p.product_id, p.barcode, p.description, p.size, s.name, " +
				"p.shelf_life, p.three_month, p.creation_date, pc.container_id " +
				"FROM product AS p " +
				"INNER JOIN size_type AS s ON p.size_type_id = s.size_type_id " +
				"INNER JOIN products_in_containers AS pc ON p.product_id = pc.product_id";
		return databaseDAO.GetConnection().prepareStatement(sql);
	}

	@Override
	protected List<HITBase> ProcessGetRS(ResultSet rs) throws SQLException {
		List<HITBase> results = new ArrayList<HITBase>();
		Set<String> barcodes = new HashSet<String>();//keep track of duplicate product records
		
		while (rs.next()) {
			if (!barcodes.contains(rs.getString(2))) {
				barcodes.add(rs.getString(2));
				
				Product product = new Product();
				product.setId(rs.getInt(1));
				product.SetBarcode(new Barcode(rs.getString(2)));
				product.SetDescription(rs.getString(3));
				product.SetSize(Size.SizeFromString(rs.getString(5), rs.getDouble(4)));
				product.SetShelfLife(rs.getInt(6));
				product.SetThreeMonth(rs.getInt(7));
				product.SetCreation(rs.getDate(8));
				
				List<Integer> ids = new ArrayList<Integer>();
				ids.add(rs.getInt(9));
				product.SetContainerIDs(ids);
				
				results.add(product);
			}
			else {
				for (HITBase base: results) {
					Product product = (Product) base;
					
					if (product.GetBarcode().GetValue().equals(rs.getString(2))) {
						product.GetContainerIDs().add(rs.getInt(9));
					}
				}
			}
		}
		
		return results;
	}

	@Override
	protected List<PreparedStatement> InitCreateStatement(HITBase object) throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		Product product = (Product) object;
		
		String sql1 = "INSERT INTO product " +
				"(barcode, description, size, size_type_id, shelf_life, three_month, " +
				"creation_date) " +
				"VALUES (?, ?, ?, ?, ?, ?, ?)";
		String sql3 = "SELECT last_insert_rowid()";
		String sql2 = "INSERT INTO products_in_containers (product_id, container_id) " +
				"VALUES ((SELECT seq FROM sqlite_sequence WHERE name = 'product'), ?)";
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setString(1, product.GetBarcode().GetValue());
		stmt1.setString(2, product.GetDescription());
		stmt1.setDouble(3, product.GetSize().GetAmount());
		stmt1.setInt(4, product.GetSize().GetType().GetDatabaseID());
		stmt1.setInt(5, product.GetShelfLife());
		stmt1.setInt(6, product.GetThreeMonth());
		stmt1.setDate(7, new Date(product.GetCreation().getTime()));
		stmts.add(stmt1);
		
		PreparedStatement stmt3 = databaseDAO.GetConnection().prepareStatement(sql3);
		stmts.add(stmt3);
		
		for (Integer i: product.GetContainerIDs()) {
			PreparedStatement stmt = databaseDAO.GetConnection().prepareStatement(sql2);
			stmt.setInt(1, i);
			stmts.add(stmt);
		}
		
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitRemoveStatement(HITBase object) throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		
		String sql1 = "DELETE FROM product WHERE product_id = ?";
		String sql2 = "DELETE FROM products_in_containers WHERE product_id = ?";
		String sql3 = "DELETE FROM item WHERE product_id = ?";
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setInt(1, object.getId());
		stmts.add(stmt1);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmt2.setInt(1, object.getId());
		stmts.add(stmt2);
		
		PreparedStatement stmt3 = databaseDAO.GetConnection().prepareStatement(sql3);
		stmt3.setInt(1, object.getId());
		stmts.add(stmt3);
		
		return stmts;
	}

	@Override
	protected List<PreparedStatement> InitUpdateQuery(HITBase object)
			throws SQLException {
		List<PreparedStatement> stmts = new ArrayList<PreparedStatement>();
		Product product = (Product) object;
		
		String sql1 = "UPDATE product SET description = ?, size = ?, size_type_id = ?, " +
				"shelf_life = ?, three_month = ? WHERE product_id = ?";
		String sql2 = "DELETE FROM products_in_containers WHERE product_id = ?";
		String sql3 = "INSERT INTO products_in_containers (product_id, container_id) " +
				"VALUES (?, ?)";
		
		PreparedStatement stmt1 = databaseDAO.GetConnection().prepareStatement(sql1);
		stmt1.setString(1, product.GetDescription());
		stmt1.setDouble(2, product.GetSize().GetAmount());
		stmt1.setInt(3, product.GetSize().GetType().GetDatabaseID());
		stmt1.setInt(4, product.GetShelfLife());
		stmt1.setInt(5, product.GetThreeMonth());
		stmt1.setInt(6, product.getId());
		stmts.add(stmt1);
		
		PreparedStatement stmt2 = databaseDAO.GetConnection().prepareStatement(sql2);
		stmt2.setInt(1, product.getId());
		stmts.add(stmt2);
		
		for (Integer i : product.GetContainerIDs()) {
			PreparedStatement stmt3 = databaseDAO.GetConnection().prepareStatement(sql3);
			stmt3.setInt(1, product.getId());
			stmt3.setInt(2, i);
			stmts.add(stmt3);
		}
		
		return stmts;
	}
	
}
